<?php

namespace Budget\BudgetBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Budget\BudgetBundle\Entity\Family;
use Budget\BudgetBundle\Entity\Category;

class CategoryRepository extends EntityRepository
{

    public function getCategoriesForFamily(Family $family)
    {
        $qb = $this->createQueryBuilder('c')
                   ->select('c')
                   ->where('c.family = :family')
                   ->andWhere('c.date_deleted IS NULL')
                   ->addOrderBy('c.name')
                   ->setParameter('family', $family->getId());

        return $qb->getQuery()->getResult();
    }

    public function getCategoriesForFamilyToJson(Family $family)
    {     
        $qb = $this->createQueryBuilder('c')
                   ->select('c')
                   ->where('c.family = :family')
                   ->andWhere('c.date_deleted IS NULL')
                   ->addOrderBy('c.name')
                   ->setParameter('family', $family->getId());
        
        $c          = $qb->getQuery()->getArrayResult();
        $categories = array();

        foreach ($c AS $category) {  
            $categories[] = $category['id'].':'.$category['name'];
        }
        
        return implode(';', $categories); 
    }
    
    public function getCategoriesForFamilyToJsonTree(Family $family)
    {     
        $qb = $this->createQueryBuilder('c')
                   ->select('c')
                   ->where('c.family = :family')
                   ->andWhere('c.date_deleted IS NULL')
                   ->addOrderBy('c.name')
                   ->setParameter('family', $family->getId());
        
        $category   = $qb->getQuery()->getResult();
        $categories = array();

        foreach ($category AS $c) {  
            if ($c->getParent()) {
               $parent = 'tree_'.$c->getParent()->getId();
            } else {
                $parent = '#';
            }
               
            $categories[] = array('id' => 'tree_'.$c->getId(), 'parent' => $parent, 'text' => $c->getName());
        }
        return $categories; 
    }
    
    public function getExpensesForFamily(Family $family)
    {   
        $qb = $this->createQueryBuilder('c')
                   ->select('c', 'e')
                   ->leftJoin('c.expenses', 'e')
                   ->where('c.family = :family')
                   ->andWhere('c.date_deleted IS NULL')
                   ->andWhere('e.date_deleted IS NULL')
                   ->addOrderBy('c.name')
                   ->setParameter('family', $family->getId());
        return $qb->getQuery()->getResult();  
    }
    
    public function findIdCategoriesForFamily(Family $family, $categories)
    {     
        $categoriesArray = explode(',', $categories);
        $qb = $this->createQueryBuilder('c')
                   ->select('c')
                   ->where('c.family = :family')
                   ->andWhere('c.id IN (:categories)' )
                   ->andWhere('c.date_deleted IS NULL')
                   ->setParameter('family', $family->getId())
                   ->setParameter('categories', array_values($categoriesArray));
        return $qb->getQuery()->getArrayResult();
    }
    
    public function checkActiveChildren(Category $category)
    {     
        $qb = $this->createQueryBuilder('c')
                   ->select('c')
                   ->where('c.parent = :parent' )
                   ->andWhere('c.date_deleted IS NULL')
                   ->setParameter('parent', $category->getId());
        $result = $qb->getQuery()->getArrayResult();
        return count($result);
    }
 
    public function findByIdForFamily(Family $family, $id)
    {
        return $this->findOneBy(array('id' => $id, 'date_deleted' => null, 'family' => $family->getId()));
    }
}
